% Data for Germany-ROW case
% clear all
% close all
% setup_workspace
[num,txt,raw] = xlsread('DEU_ROW.xlsx');

% data are from 1974 to 2019Q4 but missing data for hours for ROW from
% 2016Q4
% with interest rate, data need to start fromm 1979Q2 (Ts=22)

% order of variables

% 1 Year
% 2 Quarter
% 3 YQ
% 4 gr RGDP DEU
% 5 gr PGDP DEU
% 6 gr RCon DEU
% 7 gr RInv DEU
% 8 gr Price of imports DEU
% 9 gr Price of Exports DEU
% 10 gr Hours DEU
% 11 gr TFP (annual - Fernald US)  
% 12 NX/Y DEU
% 13 REER DEU
% 14 gr RGDP ROW
% 15 gr PGDP ROW
% 16 gr Rcon ROW
% 17 gr RInv ROW
% 18 gr Price of imports ROW
% 19 gr Price of exports ROW
% 20 gr Hours ROW
% 21 DEU Interest Rate 
% 22 ROW Interest Rate (no data using all 13 countries available)
% 23 ROW Interest Rate starting from 1974 (adjusted weights for missing)
% 24 DEU 5 year yield
% 25 DEU 10 yeard yield
% 26 ROW 5 year yield
% 27 ROW 10 year yield
% 28 DEU spread
% 29 ROW spread
% 30 DEU real exports
% 31 DEU real imports
% 32 gr of TFP ROW
% 33 gr of TFP DEU we constructed
% 34 gr of TFP ROW adjusted 
% 35 gr of TFP DEU adjusted 
% 36 Global factor from Helene Ray Restud paper
% 37 Commodity price weigh by export shares of commodity
% 38 4q ahead annual growth rate of output 
% 39 4q ahead annual growth rate of consumption
% 40 constructed real exchange rate (average of quarter)
% 41 End of period short term rate DEU (3 month yield)
% 42 End of period short term rate ROW
% 43 Inflation rate CPI DEU
% 44 Inflation rate CPI ROW
% 45 End of period constructed real exchange rate
% 46 End of period constructed RER using BIS (CORRECT)
% 47 Average daily constructed RER using BIS (CORRECT)
% 48 haver daily constructed rer
% 49 new EOP constructed RER using BIS (multiplication)
% 50 new average constructed rer using BIS (multiplication)
% 51 haver rer
% 52 realized vix
% 53 price index for energy
% 54 price index for non energy 
% 55 price index for agriculture
% 56 price index for precious metals
% 57 N export/ gdp
% 58 N imports/ gdp
% 59 Nominal exchange rate (End of period constructed NER - counterpart to 49)
% 60 real stock returns 
% 61 real trade balance
% 62 real trade balance/GDP
% 63 Terms of trade

% [num1,txt1,raw1]=xlsread('spread.xlsx',2); % spread data
% num1=num1(1:end-2,:); % 1974Q1 to 2019Q4 

% Ts=72; %1991Q2 b/c of our TFP
Tstfp=2; % 1974Q2 (new TFP)
Ts=1;
Te=172; % 2016Q4
Te2=130; %2006Q2 spread stops for DEU

% [num3,txt3,raw3]=xlsread('data_CCI.xlsx'); % consumer confidence data 

Ts1=64; %1989Q4 to do with forecast
Ts_f=1; %1989Q4
Ts_f1=7; %1991Q2
Te_f=107; % 2016Q4

num=num(Ts:Te,:);
% num1=num1(Ts:Te,:);
% num3=num3(Ts:Te,:); 


% accumulate to log level for US and ROW
usdata_gr=num(:,[4 6 7 8 9 10 43]);
rowdata_gr=num(:,[14 16 17 18 19 20 44]);

usdata=cumsum(usdata_gr);
rowdata=cumsum(rowdata_gr);

% accumulate to log level for US real exports and real imports
usdata_gr1=num(:,[30 31]);
usdata1=cumsum(usdata_gr1);
logtfp_row=cumsum(num(Tstfp:end,32));
logtfp_usa=cumsum(num(Tstfp:end,33)); % our constructed TFP
logtfpua_usa=cumsum(num(Tstfp:end,35));
logtfpua_row=cumsum(num(Tstfp:end,34));

logtfp_usa=[NaN(Tstfp-Ts,1); logtfp_usa];
logtfp_row=[NaN(Tstfp-Ts,1); logtfp_row];


% labor productivity
lp_us=usdata(:,1)-usdata(:,6);
lp_row=rowdata(:,1)-rowdata(:,6);

% Interest Rate
intratediff=(num(:,21)-num(:,23))/100/4; % i-i_row
intratediff1=(num(:,21)-num(:,23))/100/4; % i-i_row from 1974
intratediff5=(num(:,24)-num(:,26))/100/4; % i-i_row 5 year
intratediff10=(num(:,25)-num(:,27))/100/4; % i-i_row 10 year
intratediffnew=(num(:,41)-num(:,42))/100/4; % i-i_row EOP

% dRER
dRER=log(num(:,49))-lagmatrix(log(num(:,49)),1);
dRER(1)=NaN;

% spread
% spread=num1(:,3)/100/4 ; % us spread
% spread=(num(:,28)-num(:,29))/100/4 ; % us - row spread
% GF=num(:,36);
% X=log(num(:,37));


data1=[usdata(:,1)-rowdata(:,1) ... % y-y_row
    usdata(:,2)-rowdata(:,2) ... % c-c_row
    usdata(:,6)-rowdata(:,6) ... % h-h_row
    num(:,43) - num(:,44) ... % pi-pi_row
    usdata(:,3)-rowdata(:,3) ... % inv - inv_row
    num(:,12) ... % nxy
    log(num(:,49))... % REER
    intratediffnew ...  % i-i_row
    ]; 

db.data1=data1';
db.data_name={'Output','Consumption','Hours Worked',...
                          'Inflation','Investment','NXY','REER','Interest Rate'};

